package com.houdoudou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.houdoudou.bean.Supplier;

public class SupplierDao {

	 Connection conn=null;
	   
		public SupplierDao(Connection conn) {
			super();
			this.conn = conn;
		}
		
   public void create(Supplier supplier) throws SQLException{
	
	   PreparedStatement pstm=null; 
	   Statement  stm=null;
	   ResultSet rs=null; 	
	   String sql=null;
	   
	   try{
		   sql = "insert  into supplier (telnum ,secret ,name ,address,owner ,serviceTelnum ,isAuth ,minuteMini ,moneyMini ,rate ,boxFeeSu ,boxFeeCu ,major ) "
	       			+ "values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
		  
	       	pstm=conn.prepareStatement(sql);
	       	pstm.setString(1, supplier.getTelnum());
	       	pstm.setString(2, supplier.getSecret());
	       	pstm.setString(3, supplier.getName());
	       	pstm.setString(4, supplier.getAddress());
	       	pstm.setString(5, supplier.getOwner());
	       	pstm.setString(6, supplier.getServiceTelnum());
	       	pstm.setInt(7, supplier.getIsAuth());
	       	pstm.setInt(8, supplier.getMinuteMini());
	       	pstm.setDouble(9, supplier.getMoneyMini());
	       	pstm.setDouble(10, supplier.getRate());
	    	pstm.setDouble(11, supplier.getBoxFeeSu());
	    	pstm.setDouble(12, supplier.getBoxFeeCu());
	    	pstm.setString(13, supplier.getMajor());
	       	pstm.executeUpdate();
	   }finally{
		   ConnFactory.closeStatment(pstm, pstm, rs);
	   }
     }		
		
   
   public List<Supplier> search(String sql) throws SQLException {
	   List<Supplier> suppliers= new ArrayList<Supplier>();
	   PreparedStatement pstm=null; 
	   Statement  stm=null;
	   ResultSet rs=null; 	

        try {
        		        	
			stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			rs=stm.executeQuery(sql);
			while(rs.next()){
				Supplier supplier= new Supplier();
				supplier.setTelnum(rs.getString("telnum"));
				supplier.setSecret(rs.getString("secret"));
				supplier.setName(rs.getString("name"));
				supplier.setAddress(rs.getString("address"));
				supplier.setOwner(rs.getString("owner"));
				supplier.setServiceTelnum(rs.getString("serviceTelnum"));
				supplier.setIsAuth(rs.getInt("isAuth"));
				supplier.setMinuteMini(rs.getInt("minuteMini"));
				supplier.setRate(rs.getDouble("rate"));
				supplier.setMoneyMini(rs.getDouble("moneyMini"));
				supplier.setBoxFeeCu(rs.getDouble("boxFeeCu"));
				supplier.setBoxFeeSu(rs.getDouble("boxFeeSu"));
				supplier.setMajor(rs.getString("major"));
				suppliers.add(supplier);
				Supplier.print("when search", supplier);
			}
        }finally{
        	ConnFactory.closeStatment(pstm, pstm, rs);
        }
	   
	   return suppliers;
   }
   
   public Supplier selectById(String supplierTelnum) throws Exception{
	   String sql="select * from supplier where telnum = "+ supplierTelnum+" ";
	   List<Supplier> suppliers= search(sql);
	   Supplier supplierExist = null;
	   if(suppliers.size()>0){ supplierExist= suppliers.get(0); 
	   }else{
		   
		   supplierExist=new Supplier();
	   }
	   return supplierExist;
   }
   
   public  int update(Supplier supplier) throws SQLException{
	   int result = 0;
	   PreparedStatement pstm=null; 
	   Statement  stm=null;
	   ResultSet rs=null; 	
	   String sql=null;
        try {
        	sql = "update supplier set secret=? ,name=? ,address=?,owner=? ,serviceTelnum=? ,isAuth=? ,minuteMini=? ,moneyMini=? ,rate=? ,boxFeeSu=? ,boxFeeCu=? ,major=?  where telnum=?";
        
        	pstm=conn.prepareStatement(sql);
        	
	       	pstm.setString(1, supplier.getSecret());
	       	pstm.setString(2, supplier.getName());
	       	pstm.setString(3, supplier.getAddress());
	       	pstm.setString(4, supplier.getOwner());
	       	pstm.setString(5, supplier.getServiceTelnum());
	       	pstm.setInt(6, supplier.getIsAuth());
	       	pstm.setInt(7, supplier.getMinuteMini());
	       	pstm.setDouble(8, supplier.getMoneyMini());
	       	pstm.setDouble(9, supplier.getRate());
	    	pstm.setDouble(10, supplier.getBoxFeeSu());
	    	pstm.setDouble(11, supplier.getBoxFeeCu());	  
	    	pstm.setString(12, supplier.getMajor());
	    	pstm.setString(13, supplier.getTelnum());
	    	result= pstm.executeUpdate();
        	}finally{
        		ConnFactory.closeStatment(pstm, pstm, rs);
        	}
        
	   return result;
   }
   
   public  void delete(Supplier supplier) throws SQLException{
	   PreparedStatement pstm=null; 
	   Statement  stm=null;
	   ResultSet rs=null; 	
	   String sql=null;
        try {
        	sql = "delete  from supplier where telnum="+supplier.getTelnum()+"";		        	
			stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			stm.executeUpdate(sql);
			
		} finally{
			ConnFactory.closeStatment(pstm, pstm, rs);
		}		
   }
}
